package com.liang.dao.bill;

import com.liang.dao.BaseDao;
import com.liang.pojo.Bill;
import com.mysql.cj.util.StringUtils;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class BillDaoImpl implements BillDao{
    /**
     * 获取订单管理总数
     * @param connection
     * @param productName
     * @param providerId
     * @param isPayment
     * @return
     */
    @Override
    public int getCount(Connection connection, String productName, Integer providerId, Integer isPayment) {
        int count = 0;
        PreparedStatement pr = null;
        ResultSet rs = null;

        if(connection!=null){
            try {
                StringBuffer stringBuffer = new StringBuffer();
                ArrayList<Object> objects = new ArrayList<>();
                stringBuffer.append("select count(1) as count from smbms_bill as s, smbms_provider as p where s.providerId=p.id");
                if(!StringUtils.isNullOrEmpty(productName)){
                    stringBuffer.append(" and productName like ?");
                    objects.add("%"+productName+"%");
                }
                if(providerId>0){
                    stringBuffer.append(" and providerId = ?");
                    objects.add(providerId);
                }
                if(isPayment>0){
                    stringBuffer.append(" and isPayment = ?");
                    objects.add(isPayment);
                }


                String sql = stringBuffer.toString();
                Object[] paramse =objects.toArray();
                rs = BaseDao.execute(connection, pr, rs, sql, paramse);
                if(rs.next()){
                    count = rs.getInt("count");
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }finally {
                BaseDao.closeResource(rs,pr,connection);
            }
        }

        return count;
    }

    @Override
    public List<Bill> getBillList(Connection connection, String productName, Integer providerId, Integer isPayment,int pageNo,int pageSize) {
        List<Bill> list = new ArrayList<>();
        PreparedStatement pr=null;
        ResultSet rs=null;
        if(connection!=null){
            try {
                StringBuffer stringBuffer = new StringBuffer();
                ArrayList<Object> objects = new ArrayList<>();
                stringBuffer.append("select s.*,p.proName as providerName from smbms_bill as s, smbms_provider as p where s.providerId=p.id");
                if(!StringUtils.isNullOrEmpty(productName)){
                    stringBuffer.append(" and productName like ?");
                    objects.add("%"+productName+"%");
                }
                if(providerId>0){
                    stringBuffer.append(" and providerId = ?");
                    objects.add(providerId);
                }
                if(isPayment>0){
                    stringBuffer.append(" and isPayment = ?");
                    objects.add(isPayment);
                }
                stringBuffer.append(" order by creationDate desc limit ?,?");
                objects.add((pageNo-1)*pageSize);
                objects.add(pageSize);

                String sql =stringBuffer.toString();
                Object[] parmas = objects.toArray();
                rs = BaseDao.execute(connection,pr,rs,sql,parmas);
                while (rs.next()){
                    Bill bill = new Bill();
                    bill.setId(rs.getInt("id"));
                    bill.setBillCode(rs.getString("billCode"));
                    bill.setProductName(rs.getString("productName"));
                    bill.setProductDesc(rs.getString("productDesc"));
                    bill.setProductUnit(rs.getString("productUnit"));
                    bill.setProductCount(rs.getBigDecimal("productCount"));
                    bill.setTotalPrice(rs.getBigDecimal("totalPrice"));
                    bill.setIsPayment(rs.getInt("isPayment"));
                    bill.setCreatedBy(rs.getInt("createdBy"));
                    bill.setCreationDate(rs.getDate("creationDate"));
                    bill.setModifyBy(rs.getInt("modifyBy"));
                    bill.setModifyDate(rs.getDate("modifyDate"));
                    bill.setProviderId(rs.getInt("providerId"));
                    bill.setProviderName(rs.getString("providerName"));
                    list.add(bill);
                }


            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                BaseDao.closeResource(rs,pr,connection);
            }
        }
        return list;
    }

    @Override
    public Bill getBillListSearch(Connection connection, String billCode,Integer id) {
        Bill bill = null;
        PreparedStatement pr=null;
        ResultSet rs=null;
        if(connection!=null){
            try {
                StringBuffer sql = new StringBuffer();
                sql.append("select s.*,p.proName as providerName from smbms_bill as s, smbms_provider as p where s.providerId=p.id");
                List<Object> params = new ArrayList<>();
                if(!StringUtils.isNullOrEmpty(billCode)){
                    sql.append(" and s.billCode=?");
                    params.add(billCode);
                }
                if(id>0){
                    sql.append(" and s.id=?");
                    params.add(id);
                }
                if(id>0){

                }
                rs = BaseDao.execute(connection, pr, rs, sql.toString(), params.toArray());
                if(rs.next()){
                    bill = new Bill();
                    bill.setId(rs.getInt("id"));
                    bill.setBillCode(rs.getString("billCode"));
                    bill.setProductName(rs.getString("productName"));
                    bill.setProductDesc(rs.getString("productDesc"));
                    bill.setProductUnit(rs.getString("productUnit"));
                    bill.setProductCount(rs.getBigDecimal("productCount"));
                    bill.setTotalPrice(rs.getBigDecimal("totalPrice"));
                    bill.setIsPayment(rs.getInt("isPayment"));
                    bill.setCreatedBy(rs.getInt("createdBy"));
                    bill.setCreationDate(rs.getDate("creationDate"));
                    bill.setModifyBy(rs.getInt("modifyBy"));
                    bill.setModifyDate(rs.getDate("modifyDate"));
                    bill.setProviderId(rs.getInt("providerId"));
                    bill.setProviderName(rs.getString("providerName"));
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                BaseDao.closeResource(rs,pr,connection);
            }
        }
        return bill;
    }

    @Override
    public Boolean addBill(Connection connection, Bill bill) {
        Boolean flag = false;
        PreparedStatement pr = null;
        if(connection!=null){
            try {
                String sql = "insert into smbms_bill (billCode,productName,productUnit,productCount,totalPrice,providerId,isPayment,createdBy,creationDate) values (?,?,?,?,?,?,?,?,?)";
                String billCode = bill.getBillCode();
                String productName = bill.getProductName();
                String productUnit = bill.getProductUnit();
                BigDecimal productCount = bill.getProductCount();
                BigDecimal totalPrice = bill.getTotalPrice();
                Integer providerId = bill.getProviderId();
                Integer isPayment = bill.getIsPayment();
                Integer createdBy = bill.getCreatedBy();
                Date creationDate = bill.getCreationDate();
                Object[] params = {billCode,productName,productUnit,productCount,totalPrice,providerId,isPayment,createdBy,creationDate};
                flag = BaseDao.execute(connection, pr, sql, params);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }finally {
                BaseDao.closeResource(null,pr,connection);
            }
        }

        return flag;
    }

    @Override
    public Boolean updateBill(Connection connection, Bill bill) {
        Boolean flag = false;
        PreparedStatement pr = null;
        if(connection!=null){
            try {
                String sql = "update smbms_bill set billCode=?,productName=?,productUnit=?,productCount=?,totalPrice=?,providerId=?,isPayment=?,modifyBy=?,modifyDate=? where id=?";
                Integer id = bill.getId();
                String billCode = bill.getBillCode();
                String productName = bill.getProductName();
                String productUnit = bill.getProductUnit();
                BigDecimal productCount = bill.getProductCount();
                BigDecimal totalPrice = bill.getTotalPrice();
                Integer providerId = bill.getProviderId();
                Integer isPayment = bill.getIsPayment();
                Integer modifyBy = bill.getModifyBy();
                Date modifyDate = bill.getModifyDate();
                Object[] params = {billCode,productName,productUnit,productCount,totalPrice,providerId,isPayment,modifyBy,modifyDate,id};
                flag = BaseDao.execute(connection, pr, sql, params);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }finally {
                BaseDao.closeResource(null,pr,connection);
            }
        }

        return flag;
    }

    @Override
    public Boolean deleteBill(Connection connection, Integer id) {
        Boolean flag = false;
        PreparedStatement pr =null;
        if(connection!=null){
            try {

                String sql = "delete from smbms_bill where id=?";
                Object[] params = {id};
                flag = BaseDao.execute(connection,pr,sql,params);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                BaseDao.closeResource(null,pr,connection);
            }
        }



        return flag;
    }
}
